from flask import Blueprint, render_template, request, jsonify, redirect, url_for
from app.config import create_db_connection

inventory_bp = Blueprint('inventory', __name__)

@inventory_bp.route('/inventory')
def inventory():
    conn, cursor = create_db_connection()
    # 读取数据库连接配置

    # 执行查询语句获取库存列表
    sql = """
    SELECT i.product_id, SUM(i.quantity) AS total_quantity, p.customer_id, c.customer_name, p.product_name, p.product_model, p.product_color, p.product_unit, p.image_path
    FROM inventory AS i 
    JOIN product_list AS p 
    ON i.product_id = p.id
    JOIN customer_list AS c
    ON p.customer_id = c.id
    GROUP BY i.product_id, p.customer_id, c.customer_name, p.product_name, p.product_model, p.product_color, p.product_unit, p.image_path
    HAVING total_quantity > 0
    ORDER BY i.product_id DESC
    """

    cursor.execute(sql)
    products = cursor.fetchall()
    # print(products)
    # 关闭数据库连接
    cursor.close()
    conn.close()

    # 渲染 HTML 模板并返回响应
    return render_template('inventory.html', products=products)
